package ac.manchester.cs.afzal.autoservd.pattern_extraction;

import ac.manchester.cs.afzal.autoservd.sql_handler.SQL_Handler;
import java.io.FileInputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

public class SQL_Handler_PE extends SQL_Handler
{

    Vector document_values, document_ids, occurrence_values, occurrence_ids,
            local_sen_ids, distinct_sen_ids, sen_ids, predicates, dependencies;

    private Properties props;

    public SQL_Handler_PE()
    {
        props = new Properties();
        try
        {
            props.load(new FileInputStream("AutoServD.properties"));
        }
        catch (Exception ex)
        {
            System.out.println("Exception while loading propoerties");
        }
    }

    public Vector get_sentences_per_semantic_type(String semantic_type)
    {
        Vector semantic_type_values = new Vector();

        document_values = new Vector();
        occurrence_values = new Vector();
        local_sen_ids = new Vector();
        sen_ids = new Vector();
        String query;

        try
        {
            query = "SELECT Value FROM __sc_" + semantic_type;
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                semantic_type_values.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting instances of SC classes : " + sqlex.getMessage());
        }

        System.out.println("Instances loaded for Class  : " + semantic_type);

        for (int i = 0; i < semantic_type_values.size(); i++)
        {
            String instance = semantic_type_values.elementAt(i).toString();
            try
            {
                query = "Select d.Value As docs,o.Value As terms, s.SenID,s.ID As Sen_ID FROM " +
                        "Documents d, occurences o,sentences s where o.Value ='" + instance + "'" +
                        " and o.Sen_ID = s.ID and o.Doc_ID = d.ID";


                statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(query);
                while (rs.next())
                {
                    document_values.add(rs.getString("docs"));
                    occurrence_values.add(rs.getString("terms"));
                    local_sen_ids.add(rs.getString("SenID"));
                    sen_ids.add(rs.getString("Sen_ID"));
                }
                statement.close();
            }
            catch (SQLException sqlex)
            {
                System.out.println("SQL Exception: Finding document name and sentence  id for each semantic type : " + sqlex.getMessage());
            }
            System.out.println("Values retrieved for   : " + instance);
        }
        return document_values;
    }

 /*
     * It retrives  the Term Values, and sentence ids (distinct and normal) for all entries
     * in Predicate table
     */
    public Vector get_predicate_table_entries()
    {
        Vector document_names = new Vector();
        local_sen_ids = new Vector();
        predicates = new Vector();
        dependencies = new Vector();

        String query;
        try
        {
            query = "SELECT p.predicate, p.dep, p.Doc_Value,p.Sen_ID As local_Sen_ID FROM " +
                    "__predicates p where p.ID > 2425 and p.predicate NOT IN ('&lt;','(10)','/'" +
                    ",'115','325','</b>')";

            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                document_names.add(rs.getString("Doc_Value"));
                local_sen_ids.add(rs.getString("local_Sen_ID"));
                predicates.add(rs.getString("predicate"));
                dependencies.add(rs.getString("dep"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding document name and sentence id for each semantic type : " + sqlex.getMessage());
        }
        return document_names;
    }

     /*
     * It retrives  the Term Values, and sentence ids (distinct and normal) for all entries
     * in Predicate table
     */
    public Vector get_details_per_predicate(String document_value, String local_sentence)
    {
        occurrence_values = new Vector();
        document_ids = new Vector();
        distinct_sen_ids = new Vector();

        String query;
        try
        {
            query = "SELECT o.Value As occurrence_value, d.ID as Doc_ID, sm.D_Sen_ID FROM " +
                    "documents d, occurences o, sentences s, sentence_mapping sm where d.Value = '" +
                    document_value + "' and d.ID = s.DocID and s.SenID = '"+local_sentence+
                    "' and s.ID = o.ID and s.ID = sm.Sen_ID";

            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                occurrence_values.add(rs.getString("occurrence_value"));
                document_ids.add(rs.getInt("Doc_ID"));
                distinct_sen_ids.add(rs.getInt("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding document name and sentence id for each semantic type : " + sqlex.getMessage());
        }
        return occurrence_values;
    }

    /*
     * It retrives  the Term Values, and sentence ids (distinct and normal) for all entries
     * in Predicate table
     */
    public Vector get_details_per_predicate()
    {
        Vector document_names = new Vector();
        local_sen_ids = new Vector();
        occurrence_ids = new Vector();
        occurrence_values = new Vector();
        document_ids = new Vector();
        distinct_sen_ids = new Vector();

        predicates = new Vector();
        dependencies = new Vector();

        String query;
        try
        {
            query = "SELECT p.predicate, p.dep, o.Value As occurrence_value, p.Doc_Value as Doc_Value," +
                    "d.ID as Doc_ID,sm.D_Sen_ID as D_Sen_ID,p.Sen_ID As local_Sen_ID FROM " +
                    "__predicates p,Documents d,Sentences s,Occurences o,sentence_mapping sm " +
                    "where p.Doc_Value = d.Value and p.Sen_ID = s.SenID " +
                    "and s.DocID = d.ID and o.ID = s.ID and sm.Sen_ID = s.ID";

            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                document_names.add(rs.getString("Doc_Value"));
                occurrence_values.add(rs.getString("occurrence_value"));
                local_sen_ids.add(rs.getString("local_Sen_ID"));
                predicates.add(rs.getString("predicate"));
                dependencies.add(rs.getString("dep"));

                document_ids.add(rs.getInt("Doc_ID"));
                distinct_sen_ids.add(rs.getInt("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding document name and sentence id for each semantic type : " + sqlex.getMessage());
        }
        return document_names;
    }

    public Vector get_predicates()
    {
        predicates = new Vector();
        String query = "";
        try
        {
            query = "SELECT distinct Predicate FROM __predicates p where " +
                    "p.predicate NOT IN ('&lt;','(10)','/','115','325'," +
                    "'</b>') and p.predicate > 'alter' order by p.Predicate";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                predicates.add(rs.getString("Predicate"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting predicates : " + sqlex.getMessage());
        }
        return predicates;
    }

       /* It retrieves the document name (PMCID), sentence id (distinct sentence ID and 
     * sentence id in document) and term value when a particular pattern is matched with
     * Sentence value 
     */
    public Vector get_details_per_predicate_from_sentences(String pattern)
    {
        document_values = new Vector();
        occurrence_values = new Vector();
        local_sen_ids = new Vector();
        distinct_sen_ids = new Vector();
        document_ids = new Vector();
        String query;
        try
        {
            query = "SELECT distinct s.SenID As local_sen_ids,d.Value As document_values,o.Value " +
                    "As occurrence_values,sm.D_Sen_ID as distinct_sen_ids,"+
                    "s.DocID as document_ids FROM sentences s, occurences o,Documents d," +
                    "sentence_mapping sm where s.Value LIKE '"+ pattern +"' and s.ID = o.ID " +
                    "and s.DocID = d.ID and s.ID = sm.Sen_ID";

           statement = connection.createStatement();
           ResultSet rs = statement.executeQuery(query);
           while (rs.next())
           {
               document_values.add(rs.getString("document_values"));
               occurrence_values.add(rs.getString("occurrence_values"));
               local_sen_ids.add(rs.getString("local_sen_ids"));
               distinct_sen_ids.add(rs.getInt("distinct_sen_ids"));
               document_ids.add(rs.getInt("document_ids"));
           }
           statement.close();
       }
       catch ( SQLException sqlex )
       {
           System.out.println("SQL Exception: Finding document name and sentence  id for each semantic type : " + sqlex.getMessage());
       }
       return document_values;
   }
    
    public void create_predicate_table()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates (ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(45) NOT NULL,Sen_ID INTEGER UNSIGNED NOT NULL," +
                    "Doc_Value VARCHAR(45) NOT NULL,Class VARCHAR(45) NOT NULL," +
                    "Dep VARCHAR(20) NOT NULL, PRIMARY KEY (ID))";

            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
        System.out.println("Table created Successfully");
    }

    public void create_arguments_dep_table()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates_arguments_dep " +
                    "(ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(20) NOT NULL,  Arg1 TEXT NOT NULL," +
                    "Arg1_X TEXT NOT NULL,Term VARCHAR(100) NOT NULL," +
                    "Dep VARCHAR(10) NOT NULL,PRIMARY KEY (`ID`))";
            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
    }

    public void create_arguments_hybrid_table()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates_arguments_hybrid " +
                    "(ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(20) NOT NULL,  Arg1 TEXT NOT NULL," +
                    "Arg2 TEXT NOT NULL,Arg1_X TEXT NOT NULL," +
                    "Arg2_X TEXT NOT NULL,Term VARCHAR(100) NOT NULL," +
                    "Dep VARCHAR(10) NOT NULL,PRIMARY KEY (`ID`))";
            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
    }

    public void create_arguments_phrase_table()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates_arguments_ps " +
                    "(ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(20) NOT NULL,  Arg1 TEXT NOT NULL," +
                    "Arg2 TEXT NOT NULL,Arg1_X TEXT NOT NULL," +
                    "Arg2_X TEXT NOT NULL,Term VARCHAR(100) NOT NULL," +
                    "PRIMARY KEY (`ID`))";
            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
    }

    public void create_arguments_phrase_gcs_table()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates_arguments_ps_gcs " +
                    "(ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(20) NOT NULL,  Arg1 TEXT NOT NULL," +
                    "Arg2 TEXT NOT NULL,Arg1_X TEXT NOT NULL," +
                    "Arg2_X TEXT NOT NULL,Term VARCHAR(100) NOT NULL," +
                    "PRIMARY KEY (`ID`))";
            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
    }

    public void create_arguments_patterns()
    {
        try
        {
            statement = connection.createStatement();
            String query = "CREATE TABLE __predicates_arguments_patterns " +
                    "(ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +
                    "Predicate VARCHAR(20) NOT NULL,Pattern VARCHAR(20) NOT NULL," +
                    " Arg1 TEXT NOT NULL,Arg2 TEXT NOT NULL,Arg1_X TEXT NOT NULL," +
                    "Arg2_X TEXT NOT NULL,Term VARCHAR(100) NOT NULL," +
                    "PRIMARY KEY (`ID`))";
            statement.executeUpdate(query);
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Creating Table : " + sqlex.getMessage());
        }
    }
}

/*                    query = "SELECT distinct d.Value as docs,n.Value as terms,s.SenID,s.ID FROM nouns n, noun_profiles np, "+
"sentences s,documents d where n.Type = 1 and CAST(n.Value AS BINARY) REGEXP '(.)*[[:upper:]]+(.)*' "+
"and n.ID = np.Noun_ID and np.Occurence_ID = s.ID and s.DocID = d.ID";
 */ // For proper Nouns